package com.gopay.common.user.dao.impl;

import java.io.Serializable;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;

import com.gopay.common.base.dao.impl.BaseDAOImpl;
import com.gopay.common.cipher.utils.SensitiveInfoUtils;
import com.gopay.common.constants.cust.CustPersonInfoConstants;
import com.gopay.common.domain.cust.CustPersonInfo;
import com.gopay.common.user.dao.CustPersonInfoQueryDAO;

/**
 * CustPersonInfoQuery DAO接口实现
 * 
 * @CustPersonInfoQueryDAOImpl.java
 * @author liuxin
 * @2013-1-5 上午9:43:39 www.gopay.com.cn Inc.All rights reserved.
 */
@Repository("custPersonInfoQueryDao")
public class CustPersonInfoQueryDAOImpl extends BaseDAOImpl<CustPersonInfo, Serializable> implements  CustPersonInfoQueryDAO {

    @Override
    public int findMobileNo(String mobileNo) {
//        final StringBuffer sql = new StringBuffer();
//         sql.append("SELECT COUNT(*)")
//            .append("  FROM USER_INFO U")
//            .append("  LEFT JOIN CUST_PERSON_INFO P")
//            .append("    ON U.CUST_ID = P.CUST_ID")
//            .append(" WHERE 1 = 1")
//            .append("   AND U.USER_STAT <> '").append(UserInfoConstants.USER_STATE_AWAIT_EMAIL_VERIFY).append("' ")
//            .append("   AND P.CUST_MOB = '").append(mobileNo).append("' ");
//         return this.findCountBySql(sql.toString());
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
//        criteria.add(Restrictions.eq("custMob",mobileNo));
        
        /**start 央行检测敏舒数据 身份证号  ssj 2017-04-11  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
        criteria.add(Restrictions.in("custMob",SensitiveInfoUtils.getAllHisCipherText(mobileNo).toArray()));
        /**end*/
        criteria.add(Restrictions.ne("custStat", CustPersonInfoConstants.CUST_STAT_WAIT_EMAIL_CERTIFY));
        return this.findCountByCriteria(criteria);
    }

    @Override
    public CustPersonInfo findByMobileNo(String mobileNo) {
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
//        criteria.add(Restrictions.eq("custMob",mobileNo));
        /**start 央行检测敏舒数据 身份证号  ssj 2017-04-11  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
        criteria.add(Restrictions.in("custMob",SensitiveInfoUtils.getAllHisCipherText(mobileNo).toArray()));
        /**end*/
        
        criteria.add(Restrictions.eq("regType","02"));//手机注册
        criteria.add(Restrictions.ne("custStat", CustPersonInfoConstants.CUST_STAT_WAIT_EMAIL_CERTIFY));
        List<CustPersonInfo> list = this.findByCriteria(criteria);
        return list.size()>0 ? list.get(0): null;
    }
    @Override
    public CustPersonInfo findByMobile(String mobileNo) {
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
//        criteria.add(Restrictions.eq("custMob",mobileNo));
        
        /**start 央行检测敏舒数据 身份证号  ssj 2017-04-11  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
        criteria.add(Restrictions.in("custMob",SensitiveInfoUtils.getAllHisCipherText(mobileNo).toArray()));
        /**end*/
        
        List<CustPersonInfo> list = this.findByCriteria(criteria);
        return list.size()>0 ? list.get(0): null;
    }

    @Override
    public int findByCertTypeAndCertNo(String certType, String certNo) {
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
        criteria.add(Restrictions.eq("certType",certType));
//        criteria.add(Restrictions.eq("certNo",certNo));
        
        /**start 央行检测敏舒数据 身份证号  ssj 2017-04-11  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
        criteria.add(Restrictions.in("certNo",SensitiveInfoUtils.getAllHisCipherText(certNo).toArray()));
        /**end*/
        
        return this.findCountByCriteria(criteria);
    }
    
    /**
     * 检查证件号码是否已被实名认证过
     * @param certType
     * @param certNo
     * @return
     */
    @Override
    public boolean checkCertRealNamed(String certType, String certNo){
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
        criteria.add(Restrictions.eq("certType",certType));
//        criteria.add(Restrictions.eq("certNo",certNo));
        
      /**start 央行检测敏舒数据 身份证号  ssj 2017-04-11  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
      criteria.add(Restrictions.in("certNo",SensitiveInfoUtils.getAllHisCipherText(certNo).toArray()));
      /**end*/
      
        criteria.add(Restrictions.eq("isRealNameCertify","1"));
        return this.findCountByCriteria(criteria)>0;
    }

    @Override
    public CustPersonInfo findByEmail(String email) {
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
        criteria.add(Restrictions.eq("email",email).ignoreCase());
        criteria.add(Restrictions.ne("custStat", CustPersonInfoConstants.CUST_STAT_WAIT_EMAIL_CERTIFY));
        List<CustPersonInfo> list = this.findByCriteria(criteria);
        return list.size()>0 ? list.get(0): null;
    }
	
	@Override
    public CustPersonInfo findByEmail(String email,String custName,String certType) {
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
        criteria.add(Restrictions.eq("email",email).ignoreCase());
        if (custName != null && "".equals(custName)){
            criteria.add(Restrictions.eq("custName",custName).ignoreCase());
        }
        if (certType != null && "".equals(certType)){
            criteria.add(Restrictions.eq("certType",certType).ignoreCase());
        }
        criteria.add(Restrictions.ne("custStat", CustPersonInfoConstants.CUST_STAT_WAIT_EMAIL_CERTIFY));
        List<CustPersonInfo> list = this.findByCriteria(criteria);
        return list.size()>0 ? list.get(0): null;
    }
    
    @Override
    public List<CustPersonInfo> findByCertNo(String custName, String certType, String certNo) {
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
        criteria.add(Restrictions.eq("custName",custName));
        criteria.add(Restrictions.eq("certType",certType));
//        criteria.add(Restrictions.eq("certNo",certNo));
        
        /**start 央行检测敏舒数据 身份证号  ssj 2017-04-11  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
        criteria.add(Restrictions.in("certNo",SensitiveInfoUtils.getAllHisCipherText(certNo).toArray()));
        /**end*/
        
        criteria.add(Restrictions.eq("isRealNameCertify","1"));
        criteria.add(Restrictions.eq("custStat",CustPersonInfoConstants.CUST_STAT_NORMAL));
        criteria.addOrder(Order.desc("createdTs"));//排序
        List<CustPersonInfo> list = this.findByCriteria(criteria);
        return list;
    }

    @Override
    public List<CustPersonInfo> findCustPersonOpen(String email) {
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
        String[] stat ={CustPersonInfoConstants.CUST_STAT_CONSOLE_LOCKED,CustPersonInfoConstants.CUST_STAT_NORMAL};
        criteria.add(Restrictions.eq("email",email));
        criteria.add(Restrictions.in("custStat",stat));
        List<CustPersonInfo> list = this.findByCriteria(criteria);
        return list;
    }

    @Override
    public CustPersonInfo findByCustMob(String mobile) {
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
//        criteria.add(Restrictions.eq("custMob",mobile));
        
        /** start 修改人ssj  修改时间: 2017/04/10   修改原因: 央行检测 手机号进行加密处理查询  */
        criteria.add(Restrictions.in("custMob",SensitiveInfoUtils.getAllHisCipherText(mobile).toArray()));
        /**end*/
        
        criteria.add(Restrictions.ne("custStat", CustPersonInfoConstants.CUST_STAT_WAIT_EMAIL_CERTIFY));
        List<CustPersonInfo> list = this.findByCriteria(criteria);
        return list.size()>0 ? list.get(0): null;
    }
	
	@Override
    public CustPersonInfo findByCustMob(String mobile,String custName,String certType) {
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
//        criteria.add(Restrictions.eq("custMob",mobile));
        
 	   /** start 修改人ssj  修改时间: 2017/04/10   修改原因: 央行检测 手机号进行加密处理查询  */
        criteria.add(Restrictions.in("custMob",SensitiveInfoUtils.getAllHisCipherText(mobile).toArray()));
        /**end*/
      
        criteria.add(Restrictions.ne("custStat", CustPersonInfoConstants.CUST_STAT_WAIT_EMAIL_CERTIFY));
        if (custName != null && "".equals(custName)){
            criteria.add(Restrictions.eq("custName",custName));
        }
        if (certType != null && "".equals(certType)){
            criteria.add(Restrictions.eq("certType",certType));
        }
        List<CustPersonInfo> list = this.findByCriteria(criteria);
        return list.size()>0 ? list.get(0): null;
    }
	
    @Override
    public CustPersonInfo findByCustMobmobile(String mobile) {
    	DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
//    	criteria.add(Restrictions.eq("custMob",mobile));
    	
    	  /**start 央行检测敏舒数据 手机号  ssj 2017-04-11  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
        criteria.add(Restrictions.in("custMob",SensitiveInfoUtils.getAllHisCipherText(mobile).toArray()));
        /**end*/
        
    	criteria.add(Restrictions.eq("custStat", CustPersonInfoConstants.CUST_STAT_NORMAL));
    	List<CustPersonInfo> list = this.findByCriteria(criteria);
    	return list.size()>0 ? list.get(0): null;
    }
    
    @Override
    public CustPersonInfo findByCustMobAndRealNameCertify(String mobile, String isRealNameCertify) {
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
       // criteria.add(Restrictions.eq("custMob",mobile));
        /**start 央行检测敏舒数据 手机号  ssj 2017-5-5  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
        criteria.add(Restrictions.in("custMob",SensitiveInfoUtils.getAllHisCipherText(mobile).toArray()));
        /**end*/
        criteria.add(Restrictions.eq("custStat", CustPersonInfoConstants.CUST_STAT_NORMAL));
        criteria.add(Restrictions.eq("isRealNameCertify",isRealNameCertify));
        List<CustPersonInfo> list = this.findByCriteria(criteria);
        return list.size()>0 ? list.get(0): null;
    }
    
    @Override
    public CustPersonInfo findByCustMobmobileAllStatus(String mobile) {
    	DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
//    	criteria.add(Restrictions.eq("custMob",mobile));
    	
    	/**start 央行检测敏舒数据 手机号  ssj 2017-04-11  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
        criteria.add(Restrictions.in("custMob",SensitiveInfoUtils.getAllHisCipherText(mobile).toArray()));
        /**end*/
    	List<CustPersonInfo> list = this.findByCriteria(criteria);
    	return list.size()>0 ? list.get(0): null;
    }    
    /**
     * 获取最新未激活的用户数据
     */
    @Override
    public CustPersonInfo getNoActiveCustByCustMob(String mobileNo){
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
//        criteria.add(Restrictions.eq("custMob",mobileNo));
        
        /**start 央行检测敏舒数据 身份证号  ssj 2017-04-11  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
        criteria.add(Restrictions.in("custMob",SensitiveInfoUtils.getAllHisCipherText(mobileNo).toArray()));
        /**end*/
        
        criteria.add(Restrictions.eq("custStat", CustPersonInfoConstants.CUST_STAT_WAIT_EMAIL_CERTIFY));
        criteria.addOrder(Order.desc("createdTs"));
        List<CustPersonInfo> list = this.findByCriteria(criteria);
        return list.size()>0 ? list.get(0): null;
    }
    /**
     * 获取最新未激活的用户数据
     */
    @Override
    public CustPersonInfo getNoActiveCustByCustEmail(String email){
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
        criteria.add(Restrictions.eq("email",email));
        criteria.add(Restrictions.eq("custStat", CustPersonInfoConstants.CUST_STAT_WAIT_EMAIL_CERTIFY));
        criteria.addOrder(Order.desc("createdTs"));
        List<CustPersonInfo> list = this.findByCriteria(criteria);
        return list.size()>0 ? list.get(0): null;
    }

	@Override
	public CustPersonInfo getByCustId(String custId) {
		DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
        criteria.add(Restrictions.eq("custId",custId));
        //criteria.add(Restrictions.eq("custStat", CustPersonInfoConstants.CUST_STAT_NORMAL));
        criteria.addOrder(Order.desc("createdTs"));
        List<CustPersonInfo> list = this.findByCriteria(criteria);
        return list.size()>0 ? list.get(0): null;
	}
	
	@Override
    public List<CustPersonInfo> findByCustListBycertNo(String certType, String certNo, String isRealNameCertify){
		DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
	    criteria.add(Restrictions.eq("certType",certType));
//	    criteria.add(Restrictions.eq("certNo",certNo));
	    
	    /**start 央行检测敏舒数据 身份证号  ssj 2017-04-11  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
        criteria.add(Restrictions.in("certNo",SensitiveInfoUtils.getAllHisCipherText(certNo).toArray()));
        /**end*/
        
	    criteria.add(Restrictions.ne("isRealNameCertify",isRealNameCertify));
	    
	    List<CustPersonInfo> list = this.findByCriteria(criteria);
	    return list;
	}
	
	@Override
    public List<CustPersonInfo> findByCustListByPartnerId(String certNo, String name, String partnerCustId){
		DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
	    criteria.add(Restrictions.eq("custName",name));
	    
	    /**start 央行检测敏舒数据 身份证号  ssj 2017-04-11  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
        criteria.add(Restrictions.in("certNo",SensitiveInfoUtils.getAllHisCipherText(certNo).toArray()));
        /**end*/
        
	    criteria.add(Restrictions.eq("merCustId",partnerCustId));
	    
	    List<CustPersonInfo> list = this.findByCriteria(criteria);
	    return list;
	}
	
	@Override
    public List<CustPersonInfo> findByMerCustId(String merId,String MerCustId) {
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
        criteria.add(Restrictions.eq("merCustId",merId+"_"+MerCustId));
        
        return this.findByCriteria(criteria);
    }

	
	@Override
    public List<CustPersonInfo> findByCNameAndCTypeAndCNoAndRNameIn(String custName, String certType, String certNo,
            List<String> isRealNameCertifyList) {
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
        criteria.add(Restrictions.eq("custName", custName));
        criteria.add(Restrictions.eq("certType", certType));
//        criteria.add(Restrictions.eq("certNo", certNo));
        
        /**start 央行检测敏舒数据 身份证号  ssj 2017-04-11  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
        criteria.add(Restrictions.in("certNo",SensitiveInfoUtils.getAllHisCipherText(certNo).toArray()));
        /**end*/
        
        criteria.add(Restrictions.in("isRealNameCertify", isRealNameCertifyList));
        criteria.addOrder(Order.desc("createdTs"));// 排序
        List<CustPersonInfo> list = this.findByCriteria(criteria);
        return list;
    }

    @Override
    public List<CustPersonInfo> getByMobileWithoutStat(String mobile) {
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
//        criteria.add(Restrictions.eq("custMob",mobile));
        
        /**start 央行检测敏舒数据   ssj 2017-04-11  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
        criteria.add(Restrictions.in("custMob",SensitiveInfoUtils.getAllHisCipherText(mobile).toArray()));
        /**end*/
        
        return this.findByCriteria(criteria);
    }

    @Override
    public int getCertificatedCustCounts() {
        DetachedCriteria criteria = DetachedCriteria.forClass(CustPersonInfo.class);
        criteria.add(Restrictions.eq("isRealNameCertify", CustPersonInfoConstants.REAL_NAME_CERTIFY_YES));
        
        return findCountByCriteria(criteria);
    }

    @Override
    public List<CustPersonInfo> findCertifiedCustsByPage(int pageIndex, int pageSize) {
        String hql = "FROM CustPersonInfo WHERE isRealNameCertify = :isRealNameCertify";
        
        HashMap<String, Object> params = new HashMap<String, Object>();
        params.put("isRealNameCertify", CustPersonInfoConstants.REAL_NAME_CERTIFY_YES);
        
        return findByHql(hql, params, pageIndex, pageSize);
    }

    @Override
    public int getCertCustCountsByChannel(String notCertChannel) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) from cust_person_info t1");
        sql.append(" where not exists ");
        sql.append(" (select cust_id from pers_cert_channel_dtl t2 where t2.cust_id = t1.cust_id and t2.channel_code = :notCertChannel )");
        sql.append(" and t1.is_real_name_certify = :is_real_name_certify");
        
        HashMap param = new HashMap();
        param.put("notCertChannel", notCertChannel);
        param.put("is_real_name_certify", CustPersonInfoConstants.REAL_NAME_CERTIFY_YES);
        
        return findCountBySql(sql.toString(), param);
    }

    @Override
    public List<CustPersonInfo> findCertCustsByPageAndChannel(int pageIndex, int pageSize, String notCertChannel) {
        StringBuilder hql = new StringBuilder();
        hql.append(" From CustPersonInfo t1 ");
        hql.append(" where not exists ");
        hql.append(" (select t2.custId from PersCertChannelDTL t2 where t2.custId = t1.custId and t2.channelCode = :notCertChannel ) ");
        hql.append(" and t1.isRealNameCertify = :isRealNameCertify");
        hql.append(" order by custId ");
        
        HashMap param = new HashMap();
        param.put("notCertChannel", notCertChannel);
        param.put("isRealNameCertify", CustPersonInfoConstants.REAL_NAME_CERTIFY_YES);
        
        return findByHql(hql.toString(), param, pageIndex, pageSize);
    }
	
//	public List<CustPersonInfo> findByCustListBycertNo(String certType,String certNo,String isRealNameCertify){
//		final StringBuffer sql = new StringBuffer(); 
//		final Map<String,Object> param = new HashMap<String, Object>();
//			sql.append(" SELECT ")
//				.append(" P.* ")
//			   .append(" FROM ")
//			   .append(" CUST_PERSON_INFO P,USER_INFO U ")
//			   .append(" WHERE ")
//			   .append(" P.CUST_ID=U.CUST_ID")
//			   .append(" and P.CERT_NO=:certNo")
//			   .append(" and P.CERT_TYPE=:certType")
//			   .append(" and IS_REAL_NAME_CERTIFY!=:isRealNameCertify")
//			   .append(" and (U.USER_SOURCE is null or U.USER_SOURCE !=:userSource)");
//			param.put("certNo", certNo);
//			param.put("certType", certType);
//			param.put("isRealNameCertify", isRealNameCertify);
//			param.put("userSource", "58");
//				
//		return this.findBySql(sql.toString(), param);
//		
//	}


    @Override
    public List<CustPersonInfo> findCustByCertNoWithoutCancelStat(String certType, String certNo) {
        String hql = "FROM CustPersonInfo WHERE certType=:certType AND certNo IN (:certNoArr) AND custStat!=:custStat";
        Map<String,Object> queryParam = new HashMap<>(3);
        queryParam.put("certType",certType);
        queryParam.put("certNoArr",SensitiveInfoUtils.getAllHisCipherText(certNo).toArray());
        queryParam.put("custStat",CustPersonInfoConstants.CUST_STAT_CANCEL);
        return findByHql(hql,queryParam);
    }
}
